package com.cxx.dao.impl;

import com.cxx.dao.DepartmentDao;
import com.cxx.entity.Department;
import com.cxx.util.DBUtil;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.sql.*;

public class DepartmentDaoImpl implements DepartmentDao{

    @Override
    public int sava(Department dept) {
        String sql = "insert into dept value(?, ?, ?)";
        Object[] params = {dept.getDeptno(), dept.getDeptNmae(), dept.getLocation()};
        return DBUtil.executeDML(sql, params);
    }

    @Override
    public List<Department> findAll(){
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Department> list = new ArrayList<Department>();
        try {
            //2.建立和数据库的连接（url，user、password）
            conn =DBUtil.getConnection();

            //3.创建SQL命令发送器（手枪）
            pstmt = conn.prepareStatement("select * from dept");
            //4.使用SQL命令发送器发送SQL命令给数据库，并得到返回的结果（子弹）
            rs = pstmt.executeQuery();

            //5.处理结果（封装到List中）
            while(rs.next()){
                //1.取出当前行各个字段的值
                int deptno = rs.getInt("deptno");
                String deptName = rs.getString("deptName");
                String location = rs.getString("location");
                Department dept = new Department(deptno, deptName, location);
                //3.将user放入userList
                list.add(dept);
                //System.out.println(dept.toString());
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            //6.关闭资源
            DBUtil.closeAll(rs, pstmt, conn);
        }

        //7.返回数据
        return list;
    }

    @Override
    public int delete(int deptno) {
        String sql = "delete from dept where deptno = ?";
        Object[] params = {deptno};
        return DBUtil.executeDML(sql, params);
    }

    @Override
    public Department findById(int deptno) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        Department dept = null;
        try {
            //2.建立和数据库的连接（url，user、password）
            conn =DBUtil.getConnection();

            //3.创建SQL命令发送器（手枪）
            pstmt = conn.prepareStatement("select * from dept where deptno = ?");
            //4.使用SQL命令发送器发送SQL命令给数据库，并得到返回的结果（子弹）
            pstmt.setInt(1, deptno);
            rs = pstmt.executeQuery();

            //5.处理结果（封装到List中）
            if(rs.next()){
                //1.取出当前行各个字段的值

                String deptName = rs.getString("deptName");
                String location = rs.getString("location");
                dept = new Department(deptno, deptName, location);

            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            //6.关闭资源
            DBUtil.closeAll(rs, pstmt, conn);
        }

        //7.返回数据
        return dept;
    }

    @Override
    public int update(Department dept) {
        String sql = "update dept set deptname = ?, location = ? where deptno = ?";

        Object[] params = { dept.getDeptNmae(), dept.getLocation(), dept.getDeptno()};
        return DBUtil.executeDML(sql, params);
    }

}